Googleスプレッドシートで自分の出社率を計算してみた

はじめに

私は出社日を以下のようにNotionのカレンダービューに登録しています。

カレンダーをパッと見れば大体わかりますが、自分が実際月にどのくらい出社しているのかを数値的に知りたいと思いスプレッドシートを使って計算してみました。

その過程で使用したスプレッドシートの関数についてもいくつか紹介します。

データの準備

スプレッドシートの作成

スプレッドシートを新規作成し、「祝日」、「data」、「集計」の3シートを作成します。

祝日データの準備

出社率を取得するためには月の営業日数を知る必要があります。弊社は土日祝が休みですが、スプレッドシートの機能で祝日を自動的に考慮することができません。

そのため、内閣府のサイトから祝日一覧のcsvファイルを取得します。

国民の祝日について - 内閣府

「祝日」シートを開いてA1セルを選択した状態で、「ファイル」⇒「インポート」をクリックします。

「アップロード」タブを選択して、ダウンロードしたcsvファイルをドラッグアンドドロップします。

「選択したセルを先頭にデータを置換する」を選択してインポートします。

祝日一覧がインポートされました。

出社日データの準備

出社記録のカレンダービューが表示されているページで、メニューから「Export」を選択します。

Export formatを「Markdown & CSV」にして「Export」をクリックします。

csvファイルがダウンロードされるので、スプレッドシートの「data」シートに祝日一覧と同じようにインポートします。

このように出社した日付一覧がインポートされました。

データの加工

準備したデータを関数を使って加工していきますが、その前に使用した関数を簡単に説明します。

ARRAYFORMULA

ARRAYFORMULA関数を使うと、セル一つ一つに関数をコピーしなくても範囲全体に関数を適用することができます。

例えば、

=ARRAYFORMULA(A1:A6 * 2 + B1)

という関数をC1セルに入力すると、C2～C6には何も入力していないにも関わらず、A列の値を2倍してB1を足した値が自動的にそれぞれのセルに入ります。

SORT

SORT関数はその名の通り順序を並び替える関数です。以下の構文になります。

=SORT(並び替える範囲, 並び替えの基準となる列, true(昇順) or false(降順), [追加の並び替えの基準となる列（オプション）])

例えば、

=SORT(A2:D6, 3, false)

という関数は、A2からD6までを並び替えの範囲として、範囲内の3番目の列（この場合、C列）の降順（false）で並び替えます。この関数をF1セルに入力すると、元のテーブル（グレー背景）がageの降順に並び替えられたテーブル（黄色背景）がF1を始点として作成されます。

FILTER

FILTER関数は以下の構文になります。

=FILTER(フィルターする範囲, 条件, [追加の条件（オプション）])

ageが30以下という条件でフィルターする場合は、以下のようになります。

=FILTER(A2:D6, C2:C6 < 30)

UNIQUE

UNIQUE関数は範囲内の重複を排除した値を取得できます。

=UNIQUE(C2:C6)

また、

=UNIQUE(C2:D6)

のように複数の列にまたがって指定した場合、複数の列の組み合わせで一意になる値が取得できます。

EOMONTH

EOMONTH関数は指定した月の終了日を取得することができます。構文は以下の通りです。

=EOMONTH(日付, 何ヶ月後の月末を取得するか)

第二引数には、指定した日付の月の終了日を取得したければ、0を指定します。1ヶ月後の終了日を取得したければ1、1ヶ月前なら-1のように指定します。

NETWORKDAYS

NETWORKDAYS関数を使って指定した日付の範囲内に営業日が何日あるかを計算するのに使用できます。

例えば、2024/05/01～2024/05/31の土日を除いた日数を取得するには、以下のようにします。

=NETWORKDAYS("2024/05/01", "2024/05/31") 
// 結果：23

また、土日の他に除外したい日付がある場合は、第三引数に指定することで除いて計算することができます。

=NETWORKDAYS("2024/05/01", "2024/05/31", A2:A)

関数の設定

では上記の関数を使ってデータを加工していきます。

まず、月ごとに集計したいので、出社した日付を「年月」形式に変換します。

「data」シートの空いている列（この記事ではF列）の2行目に以下の関数を入力します。

=SORT({ARRAYFORMULA(IF(B2:B <> "", TEXT(B2:B, "yyyy/MM"), ""))}, 1, false)

ARRAYFORMULA関数を使っているため、B列に存在する全てのデータに対して、F列に「年月」形式に変換した値が入ります。

また、Notionからエクスポートしたデータは日付順になっていませんでした。そこでSORT関数を使ってソートしています。

これらの関数を使って、出社日を「年月」に変換し、並び替えたデータができました。

続いて「集計」シートに移ります。

まず、最終的に欲しいのは月ごとのデータなので、一意の年月一覧を取得します。以下の関数を「集計」シートのA2セルに入力します。

=UNIQUE(FILTER(data!F2:F, data!F2:F <> ""))

また、月ごとの営業日数を取得するために、月ごとの開始日と終了日を取得します。

開始日を取得するために、B2セルに以下の関数を入力します。

=ARRAYFORMULA(IF(A2:A <> "", A2:A & "/01", ""))

終了日を取得するために、C2セルに以下の関数を入力します。

=ARRAYFORMULA(IF(A2:A <> "", EOMONTH(A2:A & "/01", 0), ""))

これらの日付と、最初にインポートした祝日一覧を使って、営業日数を取得します。D2セルに以下の関数を入力します。

=ARRAYFORMULA(IF(A2:A <> "", NETWORKDAYS(B2:B, C2:C, '祝日'!A2:A), ""))

そして、Notionからエクスポートしたデータを使って「年月」ごとの件数を取得します。開始日 ≤ 日付 ≤ 終了日を満たす日付の件数をカウントしています。

=ARRAYFORMULA(IF(A2:A="", "", COUNTIFS(data!B:B, ">=" & B2:B, data!B:B, "<=" & C2:C)))

最後に出社日数 / 営業日数で出社率を計算します。

=ARRAYFORMULA(IF(A2:A <> "", E2:E / D2:D, ""))

最終的にこのようになりました。

棒グラフにしてみる

せっかくなので棒グラフにしてみます。

「挿入」⇒「グラフ」を選択します。

グラフエディタが右側に表示されるので、以下のような設定にします。

グラフが表示できました。

※上記データは年末年始休暇を除いていなかったので12月のデータが正しくないです。

今後もしもデータを更新したくなった場合は、「data」シートにインポートし直すだけで、「集計」タブの表やグラフが自動的に更新されます。

おわりに

最近はあまりExcelやスプレッドシートを使ってあれこれ計算することをしていないので、関数の使い方や知らない関数などもあって勉強になりました。

この記事が参考になれば幸いです。